﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PTCScheduler
{
    class SystemQueries
    {
        //Retrieve the database connection string
        public static String dbConnString()
        {
            return "Data Source=applicationDB.db";
        }

        //Retrieve the current date as a string
        public static String DateString(DateTime dt)
        {
            //Format the system date time to the sqlite date data type format. 
            return dt.ToString("yyyy-MM-dd HH:mm:ss");
        }

        //Retrieve all teacher names for the combox box.
        public static String teacherComboBox()
        {
            //Query to retrieve all teachers.
            return @"SELECT teachers.lname||', '||teachers.fname 'Teacher'
                            FROM teachers
                            ORDER BY teachers.lname";
        }

        //Retrieve all counselor names for the combo box.
        public static String counselorComboBox()
        {
            //Query to retrieve all counselors.
            return @"SELECT counselors.lname||', '||counselors.fname 'Counselor'
                            FROM counselors
                            WHERE counselors.counselorID <> 0
                            ORDER BY counselors.counselorID";
        }

        //Accessor method for the week query string
        public static String weekQueryString(DateTime d1, DateTime d2)
        {
            //The today query string that retrieves today's student conferences based on date. 
            return @"SELECT reminderStatus 'RS',
                                      date(date) 'Date',
                                      time(date) 'Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      counselors.email 'Counselor Contact',
                                      teachers.email 'Teacher Contact',
                                      students.parentEmail 'Parent E-Mail',
                                      students.parentPhone 'Parent Phone',
                                      comments 'Comment'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND date BETWEEN '" + DateString(d1) + "' AND '" + DateString(d2) + "' " +
                                "ORDER BY date DESC";
        }

        //Accessor method for the student query string
        public static String studentQueryString(int studentID)
        {
            //The today query string that retrieves today's student conferences based on date. 
            return @"SELECT reminderStatus 'RS',
                                      date(date) 'Date',
                                      time(date) 'Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      counselors.email 'Counselor Contact',
                                      teachers.email 'Teacher Contact',
                                      students.parentEmail 'Parent E-Mail',
                                      students.parentPhone 'Parent Phone',
                                      comments 'Comment'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND conferences.studentID = " + studentID + " " +
                                "ORDER BY date DESC";
        }

        //Retrieve today's query string
        public static String todayQueryString()
        {
            //The today query string that retrieves today's student conferences based on date. 
            return @"SELECT reminderStatus 'RS',
                                      date(date) 'Date',
                                      time(date) 'Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      counselors.email 'Counselor Contact',
                                      teachers.email 'Teacher Contact',
                                      students.parentEmail 'Parent E-Mail',
                                      students.parentPhone 'Parent Phone',
                                      comments 'Comment'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND date BETWEEN datetime('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 00:00:00" + "') AND datetime('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 23:00:00') " +
                                "ORDER BY teachers.lname";
        }

        //Retrieve teacher query string
        public static String teacherQueryString(String fname, String lname)
        {
            //The teacher query string that retrieves teachers based off of first, last, and middle names 
            return @"SELECT reminderStatus 'RS',
                                      date(date) 'Date',
                                      time(date) 'Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      counselors.email 'Counselor Contact',
                                      teachers.email 'Teacher Contact',
                                      students.parentEmail 'Parent E-Mail',
                                      students.parentPhone 'Parent Phone',
                                      comments 'Comment'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND teachers.fname = '" + fname + "' AND teachers.lname = '" + lname + "'" +
                                "ORDER BY date";
        }

        //Retrieve counselor ID query string
        public static String counselorIDQueryString(String fname, String lname)
        {
            //The query string to return the counselor id for first name and last name
            return @"SELECT counselorID FROM counselors WHERE fname = '" + fname + "' AND lname = '" + lname + "' ";
        }

        //Retrieve counselor ID query string
        public static String teacherIDQueryString(String fname, String lname)
        {
            //The query string to return the counselor id for first name and last name
            return @"SELECT teacherID FROM teachers WHERE fname = '" + fname + "' AND lname = '" + lname + "' ";
        }

        //Retrieve sutdent ID for checking whether student exists for new conference form page.
        public static String studentIDValidQueryString(int studentID)
        {
            //Query the student based off of student ID.
            return @"SELECT COUNT(*) FROM students WHERE studentID = " + studentID + "";
        }

        //Retrieve count for testing for conflicts of teachers scheduled on same day
        public static String teacherConflictQueryString(int teacherID, String date, String time)
        {
            //The query to return the count of teachers conferences for desired date.
            return @"SELECT COUNT(*) FROM conferences 
                        WHERE teacherID = " + teacherID + " AND date BETWEEN datetime('" + date + " 00:00:00') "
                                            + "AND datetime('" + date + " 23:00:00')";
        }

        //Retrieve teacher email for the email automation class.
        public static String teacherEmailQueryString(int teacherID)
        {
            //The query to retireve the teacher email based off of teacher id.
            return @"SELECT email FROM teachers
                        WHERE teacherID = " + teacherID;
        }

        //Retrieve student information for add new student page.
        public static String studentInformationQueryString(int studentID)
        {
            //The query to return all student information for given student identification number.
            return @"SELECT studentID 'Student ID',
                            fname 'First Name',
                            lname 'Last Name',
                            parentEmail 'Parent Email',
                            parentPhone 'Parent Phone'" +
                    "FROM students WHERE studentID = " + studentID;
        }

        //Retrieve teacher information for add new teacher page.
        public static String teacherInformationQueryString(String email)
        {
            //Query to return teacher.
            return @"SELECT fname 'First Name',
                            lname 'Last Name',
                            email 'Email'
                            FROM teachers WHERE email = '" + email + "'";
        }

        //Retrieve counselor information for add new teacher page.
        public static String counselorInformationQueryString(String email)
        {
            //Query to return counselor.
            return @"SELECT fname 'First Name',
                            lname 'Last Name',
                            email 'Email'
                            FROM counselors WHERE email = '" + email + "'";
        }

        //Retrieve student table for update student page.
        public static String studentTableQueryString()
        {
            //The query to return all student information for all students..
            return @"SELECT studentID 'Student ID',
                            fname 'First Name',
                            lname 'Last Name',
                            parentEmail 'Parent Email',
                            parentPhone 'Parent Phone'" +
                    "FROM students";
        }

        //Retrieve teacher table for update teacher page.
        public static String teacherTableQueryString()
        {
            //The query to return all teacher information.
            return @"SELECT fname 'First Name',
                            lname 'Last Name',
                            email 'Email'
                            FROM teachers";
        }

        //Retrieve counselor table for update counselor page.
        public static String counselorTableQueryString()
        {
            //The query to return all counselor information.
            return @"SELECT fname 'First Name',
                            lname 'Last Name',
                            email 'Email'
                            FROM counselors WHERE counselorID > 0";
        }

        //Retrieve dataset needed for reminder emails.
        //Retrieve today's query string
        public static String reminderEmailQueryString()
        {
            //The today query string that retrieves today's student conferences based on date. 
            return @"SELECT reminderStatus 'RS',
                                      date 'Date and Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      counselors.email 'Counselor Contact',
                                      teachers.email 'Teacher Contact',
                                      students.parentEmail 'Parent E-Mail',
                                      students.parentPhone 'Parent Phone',
                                      comments 'Comment',
                                      students.studentID 'Student ID'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND date BETWEEN datetime('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 00:00:00" + "') AND datetime('" + System.DateTime.Today.ToString("yyyy-MM-dd") + " 23:00:00') AND reminderStatus = 'NO' " +
                                "ORDER BY teachers.lname";
        }

        //Retrieve all conferenes scheduled for future dates. (Where date >= today's date).
        public static String futureConferencesQueryString()
        {
            //The query string to retrieve conferences based on date. 
            return @"SELECT reminderStatus 'RS',
                                      date(date) 'Date',
                                      time(date) 'Time',
                                      location 'Location',
                                      teachers.lname||', '||teachers.fname 'Teacher',
                                      students.lname||', '||students.fname 'Student',
                                      students.studentID 'Student ID',
                                      counselorNeed 'CN',
                                      counselors.lname||', '||counselors.fname 'Counselor',
                                      comments 'Comment',
                                      teachers.teacherID 'Teacher ID',
                                      teachers.email 'Teacher Email'
                                FROM conferences, students, teachers, counselors
                                WHERE conferences.teacherID = teachers.teacherID AND conferences.studentID = students.studentID AND conferences.counselorID = counselors.counselorID " +
                                "AND date >= datetime('" + DateString(System.DateTime.Now) + "') " +
                                "ORDER BY teachers.lname";
        }
    }
}
